package com.foreknow.dao.impl;

import com.foreknow.dao.DeliveryaddressDao;
import com.foreknow.po.Deliveryaddress;
import com.foreknow.util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class DeliveryaddressDaoImpl implements DeliveryaddressDao {
    private Connection con ;
    private PreparedStatement pst;
    private ResultSet rs;
    @Override
    public List<Deliveryaddress> listDeliveryAddressByUserId(String userId) throws SQLException {
        String sql = "select * from deliveryaddress where userId = ?";
        List<Deliveryaddress> list = new ArrayList<>();
        try {
            //Connection从ThreadLocal中获取
            Connection con = DBUtil.getConnection();
            pst = con.prepareStatement(sql);
            pst.setString(1,userId);
            //执行查询，并将查询到的结果放到结果集
            //遍历结果集并将每行数据保存到List集合中
            rs = pst.executeQuery();
            while (rs.next()){
                Deliveryaddress deliveryaddress = new Deliveryaddress();
                deliveryaddress.setDaId(rs.getInt("daId"));
                deliveryaddress.setContactName(rs.getString("contactName"));
                deliveryaddress.setContactSex(rs.getInt("contactSex"));
                deliveryaddress.setContactTel(rs.getString("contactTel"));
                deliveryaddress.setAddress(rs.getString("address"));
                deliveryaddress.setUserId(rs.getString("userId"));
                list.add(deliveryaddress);
            }
        }finally {                          //这里不能处理异常，也就是没有catch，只有finally
            DBUtil.close(pst);              //这里负责关闭PreparedStatement和ResultSet
        }
        return list;
    }

    @Override
    public Deliveryaddress getDeliveryAddressById(Integer daId) throws SQLException {
        String sql = "select * from deliveryaddress where daId = ?";
        Deliveryaddress deliveryaddress = new Deliveryaddress();
        try {
            //Connection从ThreadLocal中获取
            Connection con = DBUtil.getConnection();
            pst = con.prepareStatement(sql);
            pst.setInt(1,daId);
            //执行查询，并将查询到的结果放到结果集
            //遍历结果集并将每行数据保存到List集合中
            rs = pst.executeQuery();
            while (rs.next()){
                deliveryaddress.setDaId(rs.getInt("daId"));
                deliveryaddress.setContactName(rs.getString("contactName"));
                deliveryaddress.setContactSex(rs.getInt("contactSex"));
                deliveryaddress.setContactTel(rs.getString("contactTel"));
                deliveryaddress.setAddress(rs.getString("address"));
                deliveryaddress.setUserId(rs.getString("userId"));
            }
        }finally {                          //这里不能处理异常，也就是没有catch，只有finally
            DBUtil.close(pst);              //这里负责关闭PreparedStatement和ResultSet
        }
        return deliveryaddress;
    }

    @Override
    public Integer saveDeliveryAddress(String contactName, Integer contactSex, String contactTel, String address, String userId) throws SQLException {
        int result = 0;
        String sql = "insert into deliveryaddress(contactName,contactSex,contactTel,address,userId) values(?,?,?,?,?)";
        try {
            //Connection从ThreadLocal中获取
            con = DBUtil.getConnection();
            pst = con.prepareStatement(sql);
            pst.setString(1, contactName);
            pst.setInt(2, contactSex);
            pst.setString(3, contactTel);
            pst.setString(4, address);
            pst.setString(5, userId);
            result = pst.executeUpdate();
        }finally {                          //这里不能处理异常，也就是没有catch，只有finally
            DBUtil.close(pst);              //这里负责关闭PreparedStatement和ResultSet
        }
        return result;
    }

    @Override
    public Integer updateDeliveryAddress(Integer daId, String contactName, Integer contactSex, String contactTel, String address, String userId) throws SQLException {
        int result = 0;
        String sql = "update deliveryaddress set contactName=?,contactSex=?,contactTel=?,address=?,userId=? where daId=?";
        try {
            //Connection从ThreadLocal中获取
            con = DBUtil.getConnection();
            pst = con.prepareStatement(sql);
            pst.setString(1, contactName);
            pst.setInt(2, contactSex);
            pst.setString(3, contactTel);
            pst.setString(4, address);
            pst.setString(5, userId);
            pst.setInt(6, daId);
            result = pst.executeUpdate();
        }finally {                          //这里不能处理异常，也就是没有catch，只有finally
            DBUtil.close(pst);              //这里负责关闭PreparedStatement和ResultSet
        }
        return result;
    }

    @Override
    public Integer removeDeliveryAddress(Integer daId) throws SQLException {
        int result = 0;
        String sql = "delete from deliveryaddress where daId=?";
        try {
            //Connection从ThreadLocal中获取
            con = DBUtil.getConnection();
            pst = con.prepareStatement(sql);
            pst.setInt(1, daId);
            result = pst.executeUpdate();
        }finally {                          //这里不能处理异常，也就是没有catch，只有finally
            DBUtil.close(pst);              //这里负责关闭PreparedStatement和ResultSet
        }
        return result;
    }
}
